package cn.edu.sdjzu.xg.eagle.dao;

import cn.edu.sdjzu.xg.eagle.Service.TeacherDaoService;
import cn.edu.sdjzu.xg.eagle.entity.Condition;
import cn.edu.sdjzu.xg.eagle.entity.Department;
import cn.edu.sdjzu.xg.eagle.entity.Pagination;
import cn.edu.sdjzu.xg.eagle.entity.Teacher;
import cn.edu.sdjzu.xg.eagle.exception.BaseException;
import cn.edu.sdjzu.xg.eagle.factory.DaoFactory;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;


public class TeacherDaoImpl implements TeacherDaoService {

    private static TeacherDaoImpl teacherDaoImpl = new TeacherDaoImpl();

    private TeacherDaoImpl() {}

    public static TeacherDaoImpl getInstance() {
        return teacherDaoImpl;
    }

    // 根据 id 查询
    @Override
    public Teacher findTeacher(Long id) throws SQLException, BaseException {
        Teacher teacher = null;
        Connection conn = JdbcHelper.getConn();
        String sql = "select * from teacher where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        ResultSet rs = ps.executeQuery();
        try {

            if (rs.next()) {

                String name = rs.getString("name");
                String password = rs.getString("password");
                Long departmentId = rs.getLong("department_id");

                Department department = DepartmentDaoImpl.getInstance().findDepartment(departmentId);

                teacher = new Teacher(id, name, password, department);
            }
        }finally {
            JdbcHelper.close(rs, ps);
        }
        return teacher;
    }

    // 全部查询(无条件)
    @Override
    public Collection<Teacher> findTeachers() throws SQLException, BaseException {
        Connection conn = JdbcHelper.getConn();
        String sql = "select * from teacher";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        Collection<Teacher> teachers = new HashSet<Teacher>();
        while (rs.next()) {

            Long id = rs.getLong("id");
            String name = rs.getString("name");
            String password = rs.getString("password");
            Long departmentId = rs.getLong("department_id");

            Department department = DepartmentDaoImpl.getInstance().findDepartment(departmentId);

            Teacher teacher = new Teacher(id, name, password, department);

            teachers.add(teacher);
        }
        JdbcHelper.close(rs, ps);
        return teachers;
    }

    @Override
    public Collection<Teacher> findTeachersWithPagination(List<Condition> conditionList, Pagination pagination, Connection connection)
            throws SQLException, BaseException {

        //创建Teacher类型的集合类对象
        Collection<Teacher> desireTeachers = new HashSet<>();

        //查询主语句
        StringBuilder sql = new StringBuilder("SELECT * FROM teacher");

        //条件子句
        if (conditionList != null) {
            String toWhereClause = Condition.toWhereClause(conditionList);
            sql.append(toWhereClause);
        }

        //打印生成的SQL语句，便于调试
        System.out.println("Executing SQL: " + sql.toString());

        //记录总数
        int totalNum = CommonDao.getInstance().count(connection,"teacher",conditionList);

        //分页查询
        String paginationClause = pagination.toLimitClause(totalNum);
        sql.append(" ").append(paginationClause);

        //打印生成的SQL语句，便于调试
        System.out.println("Executing SQL: " + sql.toString());

        //获得查询教师的语句对象
        PreparedStatement ps = connection.prepareStatement(sql.toString());

        //结果集
        ResultSet resultSet = ps.executeQuery();

        //
        while (resultSet.next()) {
            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            String password = resultSet.getString("password");
            Long departmentId = resultSet.getLong("department_id");

            Department department = DepartmentDaoImpl.getInstance().findDepartment(departmentId);

            Teacher teacher = new Teacher(id, name, password, department);
            desireTeachers.add(teacher);
        }
        JdbcHelper.close(resultSet, ps);
        return desireTeachers;
    }

    // 根据条件列表查询
    @Override
    public Collection<Teacher> findTeachersWithCondition(List<Condition> conditionList,Connection connection)
            throws SQLException, BaseException {

        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();

        // sql语句(主句部分)
        StringBuilder sql = new StringBuilder("select * from teacher");

        // 拼接子句
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql.append(whereClause);
        }

        System.out.println("Executing SQL: " + sql.toString());

        // 带条件的总记录数
        int totalNum = CommonDao.getInstance().count(connection,"Teacher",conditionList);

        PreparedStatement pstmt = connection.prepareStatement(sql.toString());

        //
        ResultSet resultSet = pstmt.executeQuery();

        while (resultSet.next()) {

            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            String password = resultSet.getString("password");
            Long departmentId = resultSet.getLong("department_id");

            Department department = DepartmentDaoImpl.getInstance().findDepartment(departmentId);

            Teacher teacher = new Teacher(id, name, password, department);
            desiredTeachers.add(teacher);

        }
        JdbcHelper.close(resultSet, pstmt);
        return desiredTeachers;
    }

    // 增加的方法
    @Override
    public boolean addTeacher(Teacher teacher,Connection connection) throws SQLException, BaseException {

        PreparedStatement ps = null;

        String sql = "INSERT INTO teacher (name, password) VALUES (?,?)";
        ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, teacher.getName());
        ps.setString(2, teacher.getPassword());

        int affectedRows = ps.executeUpdate();
        if (affectedRows == 0) {
            throw new SQLException("插入教师失败，未影响任何行");
        }

        try (ResultSet rs = ps.getGeneratedKeys()) {
            if (rs.next()) {
                teacher.setId(rs.getLong(1));
                return true;
            }else {
               throw new SQLException("插入教师失败，未获取到教师id");
            }
        }
    }


    // 删除的方法
    @Override
    public boolean deleteTeacher(Long id,Connection connection) throws SQLException, BaseException {
        String sql = "delete from teacher where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1, id);
        int affectedRows = ps.executeUpdate();
        return affectedRows > 0;
    }
}
